package authoritah.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;

import authoritah.utils.TimeUtils;


public class Attendance {
	private int attendanceId;
	private Event event;
	private Member member;
	private Timestamp arrive;
	private Timestamp leave;
	private int pointsEarnt;

	/////////////////////////
	// Getters and Setters //
	/////////////////////////	
	public int getAttendanceId() {
		return attendanceId;
	}
	public void setAttendanceId(int attendanceId) {
		this.attendanceId = attendanceId;
	}
	public Event getEvent() {
		return event;
	}
	public void setEvent(Event event) {
		this.event = event;
	}
	public Member getMember() {
		return member;
	}
	public void setMember(Member member) {
		this.member = member;
	}
	public Timestamp getArrive() {
		return arrive;
	}
	public void setArrive(Timestamp arrive) {
		this.arrive = arrive;
	}
	public Timestamp getLeave() {
		return leave;
	}
	public void setLeave(Timestamp leave) {
		this.leave = leave;
	}
	public int getPointsEarnt() {
		return pointsEarnt;
	}
	public void setPointsEarnt(int pointsEarnt) {
		this.pointsEarnt = pointsEarnt;
	}

	//////////////////////
	// Database methods //
	//////////////////////
	/** 
	 * Retrieves an attendance from the database file. If no attendance with supplied id exists, returns null.
	 * @param id
	 * @return
	 */
	public static Attendance getAttendance(int id){
		Attendance attendance = null;
		try {
            ParityDB db = ParityDB.getInstance();
            
            Connection conn = db.getConn();
            
            PreparedStatement ps = conn.prepareStatement("SELECT * FROM attendance WHERE attendance_id=?");
            ps.setInt(0, id);
            ResultSet rs = ps.executeQuery();
            
            // Iterate the result set, printing each column
            while (rs.next()) {            	
                attendance.setAttendanceId(rs.getInt("attendance_id"));
                attendance.setEvent(Event.getEvent(rs.getInt("event_id")));
                attendance.setMember(Member.getMember(rs.getInt("member_id")));
                attendance.setArrive(rs.getTimestamp("arrive"));
                attendance.setLeave(rs.getTimestamp("leave"));
            }            
            // Close the connection
            conn.close();
            
        }
        catch (Exception e) {
        	e.printStackTrace();
        }
        return attendance;
	}
	
	/**
	 * Stores an attendance into the database.
	 * @param attendance
	 */
	public static void saveAttendance(Attendance attendance){
		try {
			ParityDB db = ParityDB.getInstance();
            
            Connection conn = db.getConn();
            
            PreparedStatement ps = conn.prepareStatement("INSERT INTO attendance (event_id, member_id, arrive, leave) VALUES (?, ?, ?, ?)");
            ps.setInt(1, attendance.getEvent().getId());
            ps.setInt(2, attendance.getMember().getId());
            ps.setTimestamp(3, attendance.getArrive());
            ps.setTimestamp(4, attendance.getLeave());
            ps.executeUpdate();
            
            // Close the connection
            conn.close();
            
        }
        catch (Exception e) {
        	e.printStackTrace();
        }
	}
	
	/**
	 * Calculates all points earnt for a particular member.
	 */
	public static int getPointsEarnt(Member member) {
		int pointsEarnt = 0;
		try {
			ParityDB db = ParityDB.getInstance();
            
            Connection conn = db.getConn();
            
            PreparedStatement ps = conn.prepareStatement("SELECT arrive, leave FROM attendance WHERE member_id=?");
            ps.setInt(1, member.getId());
            
            ResultSet rs = ps.executeQuery();
            
            // Iterate the result set, printing each column
            while (rs.next()) {            	
                pointsEarnt += TimeUtils.calculatePoints(new Timestamp(rs.getLong("arrive")), new Timestamp(rs.getLong("leave")));
            }            
            
            // Close the connection
            conn.close();
            
        }
        catch (Exception e) {
        	e.printStackTrace();
        }
        return pointsEarnt;
	}
}
